﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HU8.Helpers.Base;
using System.Data.SqlClient;
using System.Data;
using System.Text.RegularExpressions;

namespace HU8.Helpers.Files
{
    /// <summary>
    /// 作者：刘勇
    /// 数据库的一些帮助功能
    /// </summary>
    public class DatabaseHelper
    {
        /// <summary>
        /// 建立数据库连接串
        /// </summary>
        /// <param name="server"></param>
        /// <param name="database"></param>
        /// <param name="userId"></param>
        /// <param name="password"></param>
        /// <param name="trustedConnection"></param>
        /// <returns></returns>
        public static string BuildConnectionString(string server, string database, string userId, string password)
        {
            string connectionString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Persist Security Info=True;Password={3}", server, database, userId, password);
            return connectionString;
        }

        #region test
        //测试数据库连接
        public static JsonResponse TestDBConnection(string connectionString)
        {
            JsonResponse response = new JsonResponse();
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                response.message = "SQL Server 数据库连接成功";
                response.success = true;
                connection.Close();
            }
            catch (Exception ex)
            {
                connection.Close();
                response.success = false;
                response.message = "SQL Server 数据库连接失败：" + ex.Message;
            }
            return response;
        }

        /// <summary>
        /// 检查数据库是否存在
        /// </summary>
        /// <param name="database"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static int TestDBExists(string database, string connectionString)
        {
            int result = 0;
            try
            {
                string commandText = string.Format("SELECT DB_ID('{0}')", database);
                result = ExcuteReader(commandText, connectionString);
            }
            catch (Exception)
            {
                return result;
            }
            return result;
        }

        #endregion

        #region execute
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="commandText">t-sql</param>
        public static JsonResponse ExcuteSQL(string commandText, string connectionString)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
                connection.Open();


                SqlCommand sqlCommand = new SqlCommand(commandText, connection);

                sqlCommand.ExecuteNonQuery();
                sqlCommand.Connection.Close();

                response.success = true;
                response.message = string.Format("SQL语句[{0}]执行成功", commandText);
            }
            catch (Exception e)
            {
                response.success = false;
                response.message = e.Message;
            }
            return response;
        }

        /// <summary>
        /// 执行TSQL并返回查询结果的第一行第一列的值
        /// </summary>
        /// <param name="commandText">TSQL</param>
        /// <param name="connectionString">连接串</param>
        /// <returns></returns>
        public static int ExcuteReader(string commandText, string connectionString)
        {
            int result = 0;
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
                SqlCommand sqlCommand = new SqlCommand(commandText, connection);
                sqlCommand.Connection.Open();
                result = Convert.ToInt32(sqlCommand.ExecuteScalar());
                sqlCommand.Connection.Close();
                return result;
            }
            catch (Exception)
            {
                return result;
            }
        }


        /// <summary>
        /// 运行SQL文件
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="sqlFile">数据库文件</param>
        /// <returns></returns>
        public static JsonResponse RunScriptFile(string connectionString, string sqlFile)
        {
            string commandText = FileHelper.ReadTextFile(sqlFile);
            //TODO: 去除USE [Database] Command
            commandText = ReplaceUseCommend(commandText);
            //commandText = Regex.Replace(commandText, @"^USE\s+[?[\w]+]?", ""); 
            return ExecuteCommandWithSplitter(commandText, connectionString);
        }
        /// <summary>
        /// 去除USE [Database] Command
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        private static string ReplaceUseCommend(string commandText)
        {
            if (string.IsNullOrEmpty(commandText)) return string.Empty;
            var reg = new Regex(@"^\s*USE\s+[?[\w]+]?", RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.Multiline);
            commandText = reg.Replace(commandText, string.Empty);
            return commandText;
        }


        #endregion


        #region ExecuteCommandWithSplitter方法

        /// <summary>
        /// 运行含有GO命令的多条SQL命令
        /// </summary>
        /// <param name="commandText">SQL命令字符串</param>
        /// <param name="splitter">分割字符串</param>
        public static JsonResponse ExecuteCommandWithSplitter(string commandText, string splitter, string connectionString)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
                connection.Open();
                string[] commands = Regex.Split(commandText, splitter, RegexOptions.IgnorePatternWhitespace);
                foreach (string query in commands)
                {
                    if (!string.IsNullOrEmpty(query.Trim()))
                    {
                        try
                        {
                            SqlCommand sqlCommand = new SqlCommand(query, connection);
                            sqlCommand.ExecuteNonQuery();
                            response.message += "成功：" + (sqlCommand.CommandText.Length >= 50 ? sqlCommand.CommandText.Substring(50) : sqlCommand.CommandText) + "......\n";
                        }
                        catch (Exception ex)
                        {
                            response.message += "错误：" + ex.Message + "\n";
                        }
                    }
                }
                connection.Close();
                //response.message = string.Format("运行：\n{0}\n运行完毕", commandText);
                response.success = true;
            }
            catch (Exception ex)
            {
                response.message = ex.Message;
                response.success = false;
            }
            return response;
        }

        /// <summary>
        /// 运行含有GO命令的多条SQL命令
        /// </summary>
        /// <param name="commandText">SQL命令字符串</param>
        public static JsonResponse ExecuteCommandWithSplitter(string commandText, string connectionString)
        {
            return ExecuteCommandWithSplitter(commandText, "\r\nGO\r\n", connectionString);
        }
        #endregion ExecuteCommandWithSplitter方法结束


        /// <summary>
        /// 检测数据库版本
        /// </summary>
        /// <param name="commandText">t-sql</param>
        /// <param name="connectionString">数据库连接串</param>
        public static string GetSqlVersion(string connectionString)
        {
            string sqlVersion = "";
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                if (connection.State == ConnectionState.Open)
                    connection.Close();
                connection.ConnectionString = connectionString;
                connection.Open();
                SqlCommand sqlCommand = new SqlCommand("SELECT @@VERSION", connection);
                sqlVersion = sqlCommand.ExecuteScalar().ToString().Trim();
                sqlCommand.Connection.Close();
            }
            catch (Exception)
            {
            }
            return sqlVersion;
        }


        #region database creation/delete

        /// <summary>
        /// 检查用户是否有建立数据库的权限
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static JsonResponse CheckDBCreationPermission(string connectionString)
        {
            string commandText = string.Format("CREATE DATABASE [{0}]; DROP DATABASE [{0}]", "hu8website_test12345");
            return ExcuteSQL(commandText, connectionString);
        }

        /// <summary>
        /// 创建数据库
        /// </summary>
        public static JsonResponse CreateDatabase(string database, string connectionString)
        {
            JsonResponse response = new JsonResponse();
            //如果数据库不存在
            if (TestDBExists(database, connectionString) == 0)
            {
                string commandText = string.Format("CREATE DATABASE [{0}]", database);
                return ExcuteSQL(commandText, connectionString);
            }
            else
            {
                response.success = true;
                response.message = string.Format("数据库[{0}]已经存在", database);
            }
            return response;
        }

        /// <summary>
        /// 删除数据库
        /// </summary>
        /// <param name="database"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static JsonResponse DeleteDatabase(string database, string connectionString)
        {
            JsonResponse response = new JsonResponse();
            if (TestDBExists(database, connectionString) == 0)
            {
                string commandText = string.Format("DELETE DATABASE [{0}]", database);
                return ExcuteSQL(commandText, connectionString);
            }
            else
            {
                response.success = true;
                response.message = string.Format("数据库[{0}]不存在", database);
            }
            return response;
        }

        #endregion

    }
}
